/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sadhar.sipp.nilaipatokanharga;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.sadhar.errhandler.ClassAntiNull;
import org.sadhar.sia.common.ClassConnection;
import org.sadhar.sia.common.ClassUtility;

/**
 *
 * @author Deny Prasetyo
 */
public class NilaiPatokanHargaDAOImpl implements NilaiPatokanHargaDAO {

    public List<Map> listDetails(String tahun) {
        String sql = "SELECT nph.tahun,nph.tglBeli,hb.hargaBeliSatuanPerKecil,hb.kodeHargabarang, " +
                " b.kodeBarang,b.namaBarang,b.kodeSatuanBarang,jb.jenisBarang " +
                " FROM ppmk.nilaiPatokanHarga nph " +
                " inner join asset.hargaBarang hb on (nph.idHargaBarang=hb.idHargaBarang) " +
                " inner join asset.barang b on (hb.idBarang=b.idBarang) " +
                " left join kamus.jenisBarang jb on (jb.idJenisBarang = b.idJenisBarang) " +
                " where nph.tahun=?";
        return ClassConnection.getJdbc().queryForList(sql, new Object[]{ClassUtility.quote(tahun)});
    }

	@Override
	public List<NilaiPatokanHarga> listByTahunAndTanggalBeliMax(String tahun,
			Date tglBeliMax) {
         /*  String sql = "SELECT p.idHargaBarang, hb.idBarang, max(p.tglNota) as maxnota " +
				" FROM asset.pembelianBarang p inner join asset.hargaBarang hb on (p.idHargaBarang = hb.idHargaBarang)" +
				" where " +
				" year(p.tglNota) = ?  and p.tglNota <= ? " +
				"  group by hb.idBarang asc;";
         */
	/*	String sql = "SELECT p.idHargaBarang, max(p.tglNota) as maxnota " +
				" FROM asset.pembelianBarang p " +
				" where " +
				" year(p.tglNota) = ?  and p.tglNota <= ? " +
				" group by p.idHargaBarang asc";
         */

                String sql = " select sub.idHargaBarang,sub.idBarang,sub.tglNota as maxnota from " +
                             "(select hb.idHargaBarang as idHargaBarang,hb.idBarang as idBarang, p.tglNota as tglNota " +
                             " from asset.pembelianBarang as p inner join asset.hargaBarang hb on (p.idHargaBarang=hb.idHargaBarang) " +
                             " where tglNota<=?) as sub " +
                             " inner join " +
                             " (select hb.idBarang as idBarang, max(p.tglNota) as tglNota " +
                             " from asset.pembelianBarang as p inner join asset.hargaBarang hb on (p.idHargaBarang=hb.idHargaBarang) " +
                             " where tglNota<=? group by idBarang) as sub2" +
                             " on sub.idBarang=sub2.idBarang and sub.tglNota=sub2.tglNota";

		List<Map> result = ClassConnection.getJdbc().queryForList(sql, new Object[]{tglBeliMax,tglBeliMax});


		List<NilaiPatokanHarga> listNilaiPatokan = new ArrayList<NilaiPatokanHarga>();

		for(Map m: result){
			NilaiPatokanHarga nph = new NilaiPatokanHarga();
			nph.setTahun(tahun);
			nph.setIdHargaBarang(ClassAntiNull.AntiNullInt(m.get("idHargaBarang")));
			nph.setTglBeli(ClassAntiNull.AntiNullDate(m.get("maxnota")));
			listNilaiPatokan.add(nph);
		}

		return listNilaiPatokan;
	}

	@Override
	public void insert(NilaiPatokanHarga ph) {
		String sql = "insert into ppmk.nilaiPatokanHarga (tahun,idHargaBarang,tglBeli) values (?,?,?) ";
		ClassConnection.getJdbc().update(sql, new Object[]{ClassUtility.quote(ph.getTahun()),Integer.valueOf(ph.getIdHargaBarang()),ph.getTglBeli()});

	}

	@Override
	public void deleteByTahun(String tahun) {
		String sql = "delete from ppmk.nilaiPatokanHarga where tahun=? ";
		ClassConnection.getJdbc().update(sql, new Object[]{ClassUtility.quote(tahun)});


	}
}
